﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using FbKPI.Model;
namespace FbKPI.Controller
{
    public class KPIController : FbKPI.Controller.dbConnection.ConnectionClass
    {
        public string Save(KPI KPIs, string FlagInsertKPIUser)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_INSERTUPDATEKPI";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
            command.Parameters.Add("@DESCRIPTION", SqlDbType.VarChar).Value = KPIs.Description;
            command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIs.isActive;
            command.Parameters.Add("@GROUPID", SqlDbType.Int).Value = KPIs.GroupKPI == null ? 0 : KPIs.GroupKPI.ID;
            command.Parameters.Add("@CATEGORYID", SqlDbType.Int).Value = KPIs.CategoryKPI == null ? 0 : KPIs.CategoryKPI.ID;
            command.Parameters.Add("@TYPEID", SqlDbType.Int).Value = KPIs.TypeKPI == null ? 0 : KPIs.TypeKPI.ID;
            command.Parameters.Add("@SUBTYPEID", SqlDbType.Int).Value = KPIs.SubTypeKPI == null ? 0 : KPIs.SubTypeKPI.ID;
            command.Parameters.Add("@YEAR", SqlDbType.Int).Value = KPIs.Year;
            command.Parameters.Add("@PERIODE", SqlDbType.VarChar).Value = KPIs.Periode;
            command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KPIs.Polarization;
            command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KPIs.UoM == null ? 0 : KPIs.UoM.ID;
            command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KPIs.UoMDetailMax == null ? null : KPIs.UoMDetailMax.ID.ToString();
            command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KPIs.UoMDetailMin == null ? null : KPIs.UoMDetailMin.ID.ToString();
            command.Parameters.Add("@TARGETDESCRIPTION", SqlDbType.VarChar).Value = KPIs.TargetDescription;
            command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KPIs.ManualCalculation;
            command.Parameters.Add("@KPICALCULATIONTYPE", SqlDbType.VarChar).Value = KPIs.KPICalculationType;
            command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KPIs.Weight;
            command.Parameters.Add("@KPIPARENTID", SqlDbType.VarChar).Value = KPIs.KPIParent == null ? null : KPIs.KPIParent.ID.ToString();
            command.Parameters.Add("@KPIREPLACEMENTID", SqlDbType.VarChar).Value = KPIs.KPIReplacement == null ? null : KPIs.KPIReplacement.ID.ToString();
            command.Parameters.Add("@REPLACEMENTDATE", SqlDbType.Date).Value = KPIs.ReplacementDate;
            command.Parameters.Add("@REPLACEMENTDESCRIPTION", SqlDbType.VarChar).Value = KPIs.ReplacementDescription;
            command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KPIs.WeightEnableFlag;
            command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KPIs.UoMEnableFlag;
            command.Parameters.Add("@KPIDETAILINFORMATION", SqlDbType.VarChar).Value = KPIs.KPIDetailInformation;
            command.Parameters.Add("@KPISTRATEGICOBJECTIVE", SqlDbType.VarChar).Value = KPIs.KPIStrategicObjective;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
            command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIs.CreateBy;
            command.Parameters.Add("@SCOREDBYSYSTEM", SqlDbType.VarChar).Value = KPIs.ScoredBySystem;
            command.Parameters.Add("@SEQUENCE", SqlDbType.VarChar).Value = KPIs.Sequence;
            command.Parameters.Add("@EMPLOYEEIDVERIFIER", SqlDbType.VarChar).Value = KPIs.EmployeeIDVerifier;
            command.Parameters.Add("@BusinessGroupID", SqlDbType.VarChar).Value = KPIs.BusinessGroupID;
            command.Parameters.Add("@FlagInsertKPIUser", SqlDbType.VarChar).Value = FlagInsertKPIUser;
            command.Parameters.Add("@isLockWeight", SqlDbType.VarChar).Value = KPIs.isLockWeight;
            command.Parameters.Add("@AssignWeight", SqlDbType.VarChar).Value = KPIs.AssignWeight;
            command.Parameters.Add("@PercentageMax", SqlDbType.VarChar).Value = KPIs.PercentageMax;
            command.Parameters.Add("@PercentageMin", SqlDbType.VarChar).Value = KPIs.PercentageMin;
            command.Parameters.Add("@isSummaryValue", SqlDbType.VarChar).Value = KPIs.isSummaryValue;
            command.Parameters.Add("@SignEmployeeID", SqlDbType.VarChar).Value = KPIs.SignEmployeeID;


            return ExecuteNonQuery(command);
        }

        public bool SaveKPIBreakDown(KPI KPIs,string FlagInsertKPIUser,KPIAssign KPIAss,KPIAssignDetail KPIAssDtl)
        {
            bool result = true;

            string  connectionString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    //Group
                    string query = "UP_INSERTUPDATEKPI";
                    SqlCommand command = new SqlCommand(query, conn);
                    command.CommandType = CommandType.StoredProcedure;
                    //command.Parameters.Add("@KpiID", SqlDbType.VarChar).Value = KpiID;

                    //command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "UP_INSERTUPDATEKPI";
                    command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
                    command.Parameters.Add("@DESCRIPTION", SqlDbType.VarChar).Value = KPIs.Description;
                    command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIs.isActive;
                    command.Parameters.Add("@GROUPID", SqlDbType.Int).Value = KPIs.GroupKPI == null ? 0 : KPIs.GroupKPI.ID;
                    command.Parameters.Add("@CATEGORYID", SqlDbType.Int).Value = KPIs.CategoryKPI == null ? 0 : KPIs.CategoryKPI.ID;
                    command.Parameters.Add("@TYPEID", SqlDbType.Int).Value = KPIs.TypeKPI == null ? 0 : KPIs.TypeKPI.ID;
                    command.Parameters.Add("@SUBTYPEID", SqlDbType.Int).Value = KPIs.SubTypeKPI == null ? 0 : KPIs.SubTypeKPI.ID;
                    command.Parameters.Add("@YEAR", SqlDbType.Int).Value = KPIs.Year;
                    command.Parameters.Add("@PERIODE", SqlDbType.VarChar).Value = KPIs.Periode;
                    command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KPIs.Polarization;
                    command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KPIs.UoM == null ? 0 : KPIs.UoM.ID;
                    command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KPIs.UoMDetailMax == null ? null : KPIs.UoMDetailMax.ID.ToString();
                    command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KPIs.UoMDetailMin == null ? null : KPIs.UoMDetailMin.ID.ToString();
                    command.Parameters.Add("@TARGETDESCRIPTION", SqlDbType.VarChar).Value = KPIs.TargetDescription;
                    command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KPIs.ManualCalculation;
                    command.Parameters.Add("@KPICALCULATIONTYPE", SqlDbType.VarChar).Value = KPIs.KPICalculationType;
                    command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KPIs.Weight;
                    command.Parameters.Add("@KPIPARENTID", SqlDbType.VarChar).Value = KPIs.KPIParent == null ? null : KPIs.KPIParent.ID.ToString();
                    command.Parameters.Add("@KPIREPLACEMENTID", SqlDbType.VarChar).Value = KPIs.KPIReplacement == null ? null : KPIs.KPIReplacement.ID.ToString();
                    command.Parameters.Add("@REPLACEMENTDATE", SqlDbType.Date).Value = KPIs.ReplacementDate;
                    command.Parameters.Add("@REPLACEMENTDESCRIPTION", SqlDbType.VarChar).Value = KPIs.ReplacementDescription;
                    command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KPIs.WeightEnableFlag;
                    command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KPIs.UoMEnableFlag;
                    command.Parameters.Add("@KPIDETAILINFORMATION", SqlDbType.VarChar).Value = KPIs.KPIDetailInformation;
                    command.Parameters.Add("@KPISTRATEGICOBJECTIVE", SqlDbType.VarChar).Value = KPIs.KPIStrategicObjective;
                    command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
                    command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIs.CreateBy;
                    command.Parameters.Add("@SCOREDBYSYSTEM", SqlDbType.VarChar).Value = KPIs.ScoredBySystem;
                    command.Parameters.Add("@SEQUENCE", SqlDbType.VarChar).Value = KPIs.Sequence;
                    command.Parameters.Add("@EMPLOYEEIDVERIFIER", SqlDbType.VarChar).Value = KPIs.EmployeeIDVerifier;
                    command.Parameters.Add("@BusinessGroupID", SqlDbType.VarChar).Value = KPIs.BusinessGroupID;
                    command.Parameters.Add("@FlagInsertKPIUser", SqlDbType.VarChar).Value = FlagInsertKPIUser;
                    command.Parameters.Add("@isLockWeight", SqlDbType.VarChar).Value = KPIs.isLockWeight;
                    command.Parameters.Add("@AssignWeight", SqlDbType.VarChar).Value = KPIs.AssignWeight;
                    command.Parameters.Add("@PercentageMax", SqlDbType.VarChar).Value = KPIs.PercentageMax;
                    command.Parameters.Add("@PercentageMin", SqlDbType.VarChar).Value = KPIs.PercentageMin;
                    command.Parameters.Add("@isSummaryValue", SqlDbType.VarChar).Value = KPIs.isSummaryValue;
                    command.Parameters.Add("@SignEmployeeID", SqlDbType.VarChar).Value = KPIs.SignEmployeeID;


                    command.Transaction = txn;
                    command.ExecuteNonQuery();
                    command.Dispose();


                    query = "SP_GeTKPIByParentID";
                    command = new SqlCommand(query, conn);
                    command.CommandType = CommandType.StoredProcedure;
                    //command.CommandText = "UP_InsertUpdateKPIAssign";
                    command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.KPIParent.ID;
                    command.Transaction = txn;
                    object KPIId = command.ExecuteScalar();
                    command.Dispose();



                    query = "UP_InsertUpdateKPIAssign";
                    command = new SqlCommand(query, conn);
                    command.CommandType = CommandType.StoredProcedure;
                    //command.CommandText = "UP_InsertUpdateKPIAssign";
                    command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIAss.ID;
                    command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KPIId.ToString();// KPIAss.KPI.ID;
                    command.Parameters.Add("@RGMID", SqlDbType.VarChar).Value = KPIAss.RGM.ID;
                    command.Parameters.Add("@LOCATIONID", SqlDbType.VarChar).Value = KPIAss.Location == null ? 0 : KPIAss.Location.ID;
                    command.Parameters.Add("@SUBBRANCHID", SqlDbType.VarChar).Value = KPIAss.SubBranch == null ? 0 : KPIAss.SubBranch.ID;
                    //command.Parameters.Add("@VERIFIER", SqlDbType.VarChar).Value = KPIs.Verifier.Supervisor.ID;
                    command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIAss.isActive;
                    command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KPIAss.Polarization;
                    command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KPIs.UoM == null ? 0 : KPIs.UoM.ID;
                    command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KPIAss.UoMDetailMax == null ? null : KPIAss.UoMDetailMax.ID.ToString();
                    command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KPIAss.UoMDetailMin == null ? null : KPIAss.UoMDetailMin.ID.ToString();
                    command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KPIAss.ManualCalculation;
                    command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KPIAss.Weight;
                    command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KPIAss.WeightEnableFlag;
                    command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KPIAss.UoMEnableFlag;
                    command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIAss.LastModifiedBy;
                    command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIAss.CreateBy;

                    command.Transaction = txn;
                    command.ExecuteNonQuery();
                    command.Dispose();

                   

                    

                    query = "SP_GeTKPIAssignByKPIId";
                    command = new SqlCommand(query, conn);
                    command.CommandType = CommandType.StoredProcedure;
                    //command.CommandText = "UP_InsertUpdateKPIAssign";
                    command.Parameters.Add("@KPIId", SqlDbType.VarChar).Value = KPIId.ToString();
                    command.Transaction = txn;
                    object IDAssign = command.ExecuteScalar();
                    command.Dispose();


                    query = "UP_InsertUpdateKPIAssignDetail";
                    command = new SqlCommand(query, conn);
                    command.CommandType = CommandType.StoredProcedure;
                    //command.CommandText = "UP_InsertUpdateKPIAssignDetail";
                    command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIAssDtl.ID;
                    command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KPIId.ToString();// KPIAssDtl.KPI.ID;
                    command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = IDAssign.ToString();// KPIAssDtl.KPIAssign.ID;
                    command.Parameters.Add("@EMPLOYEEID", SqlDbType.VarChar).Value = KPIAssDtl.Employee.ID;
                    command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIAssDtl.isActive;
                    command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KPIAssDtl.Polarization;
                    command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KPIs.UoM == null ? 0 : KPIs.UoM.ID;
                    command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KPIAssDtl.UoMDetailMax == null ? null : KPIAssDtl.UoMDetailMax.ID.ToString();
                    command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KPIAssDtl.UoMDetailMin == null ? null : KPIAssDtl.UoMDetailMin.ID.ToString();
                    command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KPIAssDtl.ManualCalculation;
                    command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KPIAssDtl.Weight;
                    command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KPIAssDtl.WeightEnableFlag;
                    command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KPIAssDtl.UoMEnableFlag;
                    command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIAssDtl.LastModifiedBy;
                    command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIAssDtl.CreateBy;
                    command.Parameters.Add("@MANDATORY", SqlDbType.VarChar).Value = KPIAssDtl.Mandatory;
                    command.Parameters.Add("@ASSIGNBY", SqlDbType.VarChar).Value = KPIAssDtl.AssignBy;

                    command.Transaction = txn;
                    command.ExecuteNonQuery();
                    command.Dispose();


                    query = "UP_UpdateEmployeeVerifierKPI";
                    command = new SqlCommand(query, conn);
                    command.CommandType = CommandType.StoredProcedure;
                    //command.CommandText = "UP_UpdateEmployeeVerifierKPI";
                    command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIId.ToString();
                    command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
                    command.Parameters.Add("@EMPLOYEEIDVERIFIER", SqlDbType.VarChar).Value = KPIs.EmployeeIDVerifier;

                    command.Transaction = txn;
                    command.ExecuteNonQuery();
                    command.Dispose();


                    query = "SP_KPIMethod_InsertBreakDown";
                    command = new SqlCommand(query, conn);
                    command.CommandType = CommandType.StoredProcedure;
                    //command.CommandText = "UP_UpdateEmployeeVerifierKPI";
                    command.Parameters.Add("@KpiId", SqlDbType.VarChar).Value = KPIId.ToString();
                    command.Parameters.Add("@KpiParentID", SqlDbType.VarChar).Value = KPIs.KPIParent.ID;

                    command.Transaction = txn;
                    command.ExecuteNonQuery();
                    command.Dispose();

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

        public string Save(KPI KPIs)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_INSERTUPDATEKPI";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
            command.Parameters.Add("@DESCRIPTION", SqlDbType.VarChar).Value = KPIs.Description;
            command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIs.isActive;
            command.Parameters.Add("@GROUPID", SqlDbType.Int).Value = KPIs.GroupKPI == null ? 0 : KPIs.GroupKPI.ID;
            command.Parameters.Add("@CATEGORYID", SqlDbType.Int).Value = KPIs.CategoryKPI == null ? 0 : KPIs.CategoryKPI.ID;
            command.Parameters.Add("@TYPEID", SqlDbType.Int).Value = KPIs.TypeKPI == null ? 0 : KPIs.TypeKPI.ID;
            command.Parameters.Add("@SUBTYPEID", SqlDbType.Int).Value = KPIs.SubTypeKPI == null ? 0 : KPIs.SubTypeKPI.ID;
            command.Parameters.Add("@YEAR", SqlDbType.Int).Value = KPIs.Year;
            command.Parameters.Add("@PERIODE", SqlDbType.VarChar).Value = KPIs.Periode;
            command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KPIs.Polarization;
            command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KPIs.UoM == null ? 0 : KPIs.UoM.ID;
            command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KPIs.UoMDetailMax == null ? null : KPIs.UoMDetailMax.ID.ToString();
            command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KPIs.UoMDetailMin == null ? null : KPIs.UoMDetailMin.ID.ToString();
            command.Parameters.Add("@TARGETDESCRIPTION", SqlDbType.VarChar).Value = KPIs.TargetDescription;
            command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KPIs.ManualCalculation;
            command.Parameters.Add("@KPICALCULATIONTYPE", SqlDbType.VarChar).Value = KPIs.KPICalculationType;
            command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KPIs.Weight;
            command.Parameters.Add("@KPIPARENTID", SqlDbType.VarChar).Value = KPIs.KPIParent == null ? null : KPIs.KPIParent.ID.ToString();
            command.Parameters.Add("@KPIREPLACEMENTID", SqlDbType.VarChar).Value = KPIs.KPIReplacement == null ? null : KPIs.KPIReplacement.ID.ToString();
            command.Parameters.Add("@REPLACEMENTDATE", SqlDbType.Date).Value = KPIs.ReplacementDate;
            command.Parameters.Add("@REPLACEMENTDESCRIPTION", SqlDbType.VarChar).Value = KPIs.ReplacementDescription;
            command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KPIs.WeightEnableFlag;
            command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KPIs.UoMEnableFlag;
            command.Parameters.Add("@KPIDETAILINFORMATION", SqlDbType.VarChar).Value = KPIs.KPIDetailInformation;
            command.Parameters.Add("@KPISTRATEGICOBJECTIVE", SqlDbType.VarChar).Value = KPIs.KPIStrategicObjective;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
            command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIs.CreateBy;
            command.Parameters.Add("@SCOREDBYSYSTEM", SqlDbType.VarChar).Value = KPIs.ScoredBySystem;
            command.Parameters.Add("@SEQUENCE", SqlDbType.VarChar).Value = KPIs.Sequence;
            command.Parameters.Add("@EMPLOYEEIDVERIFIER", SqlDbType.VarChar).Value = KPIs.EmployeeIDVerifier;
            command.Parameters.Add("@BusinessGroupID", SqlDbType.VarChar).Value = KPIs.BusinessGroupID;
            command.Parameters.Add("@FlagInsertKPIUser", SqlDbType.VarChar).Value = "Admin";
            command.Parameters.Add("@isLockWeight", SqlDbType.VarChar).Value = KPIs.isLockWeight;
            command.Parameters.Add("@AssignWeight", SqlDbType.VarChar).Value = KPIs.AssignWeight;
            command.Parameters.Add("@PercentageMax", SqlDbType.VarChar).Value = KPIs.PercentageMax;
            command.Parameters.Add("@PercentageMin", SqlDbType.VarChar).Value = KPIs.PercentageMin;
            command.Parameters.Add("@isSummaryValue", SqlDbType.VarChar).Value = KPIs.isSummaryValue;

            return ExecuteNonQuery(command);
        }

        public bool SaveKPIByUser(KPI KPIs,Employee employee)
        {
            bool result = true;

            string connectionString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                   
                        string query = "UP_INSERTUPDATEKPI";
                        SqlCommand command = new SqlCommand(query, conn);
                        command.CommandType = CommandType.StoredProcedure;
                        //command.CommandText = "UP_INSERTUPDATEKPI";
                        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
                        command.Parameters.Add("@DESCRIPTION", SqlDbType.VarChar).Value = KPIs.Description;
                        command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIs.isActive;
                        command.Parameters.Add("@GROUPID", SqlDbType.Int).Value = KPIs.GroupKPI == null ? 0 : KPIs.GroupKPI.ID;
                        command.Parameters.Add("@CATEGORYID", SqlDbType.Int).Value = KPIs.CategoryKPI == null ? 0 : KPIs.CategoryKPI.ID;
                        command.Parameters.Add("@TYPEID", SqlDbType.Int).Value = KPIs.TypeKPI == null ? 0 : KPIs.TypeKPI.ID;
                        command.Parameters.Add("@SUBTYPEID", SqlDbType.Int).Value = KPIs.SubTypeKPI == null ? 0 : KPIs.SubTypeKPI.ID;
                        command.Parameters.Add("@YEAR", SqlDbType.Int).Value = KPIs.Year;
                        command.Parameters.Add("@PERIODE", SqlDbType.VarChar).Value = KPIs.Periode;
                        command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KPIs.Polarization;
                        command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KPIs.UoM == null ? 0 : KPIs.UoM.ID;
                        command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KPIs.UoMDetailMax == null ? null : KPIs.UoMDetailMax.ID.ToString();
                        command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KPIs.UoMDetailMin == null ? null : KPIs.UoMDetailMin.ID.ToString();
                        command.Parameters.Add("@TARGETDESCRIPTION", SqlDbType.VarChar).Value = KPIs.TargetDescription;
                        command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KPIs.ManualCalculation;
                        command.Parameters.Add("@KPICALCULATIONTYPE", SqlDbType.VarChar).Value = KPIs.KPICalculationType;
                        command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KPIs.Weight;
                        command.Parameters.Add("@KPIPARENTID", SqlDbType.VarChar).Value = KPIs.KPIParent == null ? null : KPIs.KPIParent.ID.ToString();
                        command.Parameters.Add("@KPIREPLACEMENTID", SqlDbType.VarChar).Value = KPIs.KPIReplacement == null ? null : KPIs.KPIReplacement.ID.ToString();
                        command.Parameters.Add("@REPLACEMENTDATE", SqlDbType.Date).Value = KPIs.ReplacementDate;
                        command.Parameters.Add("@REPLACEMENTDESCRIPTION", SqlDbType.VarChar).Value = KPIs.ReplacementDescription;
                        command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KPIs.WeightEnableFlag;
                        command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KPIs.UoMEnableFlag;
                        command.Parameters.Add("@KPIDETAILINFORMATION", SqlDbType.VarChar).Value = KPIs.KPIDetailInformation;
                        command.Parameters.Add("@KPISTRATEGICOBJECTIVE", SqlDbType.VarChar).Value = KPIs.KPIStrategicObjective;
                        command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
                        command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIs.CreateBy;
                        command.Parameters.Add("@SCOREDBYSYSTEM", SqlDbType.VarChar).Value = KPIs.ScoredBySystem;
                        command.Parameters.Add("@SEQUENCE", SqlDbType.VarChar).Value = KPIs.Sequence;
                        command.Parameters.Add("@EMPLOYEEIDVERIFIER", SqlDbType.VarChar).Value = KPIs.EmployeeIDVerifier;
                        command.Parameters.Add("@BusinessGroupID", SqlDbType.VarChar).Value = KPIs.BusinessGroupID;
                        command.Parameters.Add("@FlagInsertKPIUser", SqlDbType.VarChar).Value = "User";
                        command.Parameters.Add("@isLockWeight", SqlDbType.VarChar).Value = KPIs.isLockWeight;
                        command.Parameters.Add("@AssignWeight", SqlDbType.VarChar).Value = KPIs.AssignWeight;
                        command.Parameters.Add("@PercentageMax", SqlDbType.VarChar).Value = KPIs.PercentageMax;
                        command.Parameters.Add("@PercentageMin", SqlDbType.VarChar).Value = KPIs.PercentageMin;
                        command.Parameters.Add("@isSummaryValue", SqlDbType.VarChar).Value = KPIs.isSummaryValue;
                        command.Parameters.Add("@SignEmployeeID", SqlDbType.VarChar).Value = KPIs.SignEmployeeID;

                        command.Transaction = txn;
                        command.ExecuteNonQuery();
                        command.Dispose();


                        if (KPIs.ID == 0)
                        {

                            query = "SP_GetKpiID_By_CreateBy_CreateDate";
                            command = new SqlCommand(query, conn);
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Add("@CreateDate", SqlDbType.VarChar).Value = DateTime.Now.ToString("dd/MM/yyyy");
                            command.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = KPIs.CreateBy;
                            command.Transaction = txn;
                            object KPIId = command.ExecuteScalar();
                            int KpiID = KPIId == DBNull.Value ? 0 : Convert.ToInt32(KPIId);
                            command.Dispose();


                            query = "UP_InsertUpdateKPIAssign";
                            command = new SqlCommand(query, conn);
                            command.CommandType = CommandType.StoredProcedure;
                            //command.CommandText = "UP_InsertUpdateKPIAssign";
                            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = "0";
                            command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KpiID;
                            command.Parameters.Add("@RGMID", SqlDbType.VarChar).Value = employee.SubBranch.Location.RGM.ID;
                            command.Parameters.Add("@LOCATIONID", SqlDbType.VarChar).Value = employee.SubBranch.Location == null ? 0 : employee.SubBranch.Location.ID;
                            command.Parameters.Add("@SUBBRANCHID", SqlDbType.VarChar).Value = employee.SubBranch == null ? 0 : employee.SubBranch.ID;
                            //command.Parameters.Add("@VERIFIER", SqlDbType.VarChar).Value = KPIs.Verifier.Supervisor.ID;
                            command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIs.isActive;
                            command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KPIs.Polarization;
                            command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KPIs.UoM == null ? 0 : KPIs.UoM.ID;
                            command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KPIs.UoMDetailMax == null ? null : KPIs.UoMDetailMax.ID.ToString();
                            command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KPIs.UoMDetailMin == null ? null : KPIs.UoMDetailMin.ID.ToString();
                            command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KPIs.ManualCalculation;
                            command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KPIs.Weight;
                            command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KPIs.WeightEnableFlag;
                            command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KPIs.UoMEnableFlag;
                            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
                            command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIs.CreateBy;

                            command.Transaction = txn;
                            command.ExecuteNonQuery();
                            command.Dispose();

                            query = "SP_GetKpiAssignID_By_CreateBy_CreateDate";
                            command = new SqlCommand(query, conn);
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Add("@CreateDate", SqlDbType.VarChar).Value = DateTime.Now.ToString("dd/MM/yyyy");
                            command.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = KPIs.CreateBy;
                            command.Transaction = txn;
                            object KPIAssignId = command.ExecuteScalar();
                            int KpiAssignID = KPIAssignId == DBNull.Value ? 0 : Convert.ToInt32(KPIAssignId);
                            command.Dispose();


                            query = "UP_InsertUpdateKPIAssignDetail";
                            command = new SqlCommand(query, conn);
                            command.CommandType = CommandType.StoredProcedure;
                            //command.CommandText = "UP_InsertUpdateKPIAssignDetail";
                            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = 0;
                            command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KpiID;
                            command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = KpiAssignID;
                            command.Parameters.Add("@EMPLOYEEID", SqlDbType.VarChar).Value = employee.ID;
                            command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIs.isActive;
                            command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KPIs.Polarization;
                            command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KPIs.UoM == null ? 0 : KPIs.UoM.ID;
                            command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KPIs.UoMDetailMax == null ? null : KPIs.UoMDetailMax.ID.ToString();
                            command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KPIs.UoMDetailMin == null ? null : KPIs.UoMDetailMin.ID.ToString();
                            command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KPIs.ManualCalculation;
                            command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KPIs.Weight;
                            command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KPIs.WeightEnableFlag;
                            command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KPIs.UoMEnableFlag;
                            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
                            command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIs.CreateBy;
                            command.Parameters.Add("@MANDATORY", SqlDbType.VarChar).Value = 0;
                            command.Parameters.Add("@ASSIGNBY", SqlDbType.VarChar).Value = employee.ID;

                            command.Transaction = txn;
                            command.ExecuteNonQuery();
                            command.Dispose();
                        }

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;





           
        }

        public bool Save(List<KPI> list)
        {
            bool result = true;

            string connectionString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    foreach (KPI KPIs in list)
                    {
                        string query = "UP_INSERTUPDATEKPI_Import";
                        SqlCommand command = new SqlCommand(query, conn);
                        command.CommandType = CommandType.StoredProcedure;
                        //command.CommandText = "UP_INSERTUPDATEKPI";
                        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
                        command.Parameters.Add("@DESCRIPTION", SqlDbType.VarChar).Value = KPIs.Description;
                        //command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIs.isActive;
                        command.Parameters.Add("@GROUPID", SqlDbType.Int).Value = KPIs.GroupKPI == null ? 0 : KPIs.GroupKPI.ID;
                        command.Parameters.Add("@CATEGORYID", SqlDbType.Int).Value = KPIs.CategoryKPI == null ? 0 : KPIs.CategoryKPI.ID;
                        command.Parameters.Add("@TYPEID", SqlDbType.Int).Value = KPIs.TypeKPI == null ? 0 : KPIs.TypeKPI.ID;
                        command.Parameters.Add("@SUBTYPEID", SqlDbType.Int).Value = KPIs.SubTypeKPI == null ? 0 : KPIs.SubTypeKPI.ID;
                        command.Parameters.Add("@YEAR", SqlDbType.Int).Value = KPIs.Year;
                        command.Parameters.Add("@PERIODE", SqlDbType.VarChar).Value = KPIs.Periode;
                        command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KPIs.Polarization;
                        command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KPIs.UoM == null ? 0 : KPIs.UoM.ID;
                        command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KPIs.UoMDetailMax == null ? null : KPIs.UoMDetailMax.ID.ToString();
                        command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KPIs.UoMDetailMin == null ? null : KPIs.UoMDetailMin.ID.ToString();
                        //command.Parameters.Add("@TARGETDESCRIPTION", SqlDbType.VarChar).Value = KPIs.TargetDescription;
                        //command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KPIs.ManualCalculation;
                        //command.Parameters.Add("@KPICALCULATIONTYPE", SqlDbType.VarChar).Value = KPIs.KPICalculationType;
                        command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KPIs.Weight;
                        //command.Parameters.Add("@KPIPARENTID", SqlDbType.VarChar).Value = KPIs.KPIParent == null ? null : KPIs.KPIParent.ID.ToString();
                        //command.Parameters.Add("@KPIREPLACEMENTID", SqlDbType.VarChar).Value = KPIs.KPIReplacement == null ? null : KPIs.KPIReplacement.ID.ToString();
                        //command.Parameters.Add("@REPLACEMENTDATE", SqlDbType.Date).Value = KPIs.ReplacementDate;
                        //command.Parameters.Add("@REPLACEMENTDESCRIPTION", SqlDbType.VarChar).Value = KPIs.ReplacementDescription;
                        //command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KPIs.WeightEnableFlag;
                        //command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KPIs.UoMEnableFlag;
                        //command.Parameters.Add("@KPIDETAILINFORMATION", SqlDbType.VarChar).Value = KPIs.KPIDetailInformation;
                        //command.Parameters.Add("@KPISTRATEGICOBJECTIVE", SqlDbType.VarChar).Value = KPIs.KPIStrategicObjective;
                        command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
                        command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIs.CreateBy;
                        //command.Parameters.Add("@SCOREDBYSYSTEM", SqlDbType.VarChar).Value = KPIs.ScoredBySystem;
                        //command.Parameters.Add("@SEQUENCE", SqlDbType.VarChar).Value = KPIs.Sequence;
                        //
                        command.Parameters.Add("@BusinessGroupID", SqlDbType.VarChar).Value = KPIs.BusinessGroupID;
                        //command.Parameters.Add("@FlagInsertKPIUser", SqlDbType.VarChar).Value = "Admin";
                        //command.Parameters.Add("@isLockWeight", SqlDbType.VarChar).Value = KPIs.isLockWeight;
                        //command.Parameters.Add("@AssignWeight", SqlDbType.VarChar).Value = KPIs.AssignWeight;
                        //command.Parameters.Add("@PercentageMax", SqlDbType.VarChar).Value = KPIs.PercentageMax;
                        //command.Parameters.Add("@PercentageMin", SqlDbType.VarChar).Value = KPIs.PercentageMin;
                        //command.Parameters.Add("@isSummaryValue", SqlDbType.VarChar).Value = "0";
                        //command.Parameters.Add("@SignEmployeeID", SqlDbType.VarChar).Value = KPIs.SignEmployeeID;

                        command.Parameters.Add("@EMPLOYEEIDVERIFIER", SqlDbType.VarChar).Value = KPIs.EmployeeIDVerifier;

                        command.Transaction = txn;
                        command.ExecuteNonQuery();
                        command.Dispose();
                    }

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

        public string UpateVerifierKPI(KPI KPIs)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_UpdateEmployeeVerifierKPI";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
            command.Parameters.Add("@EMPLOYEEIDVERIFIER", SqlDbType.VarChar).Value = KPIs.EmployeeIDVerifier;
           

            return ExecuteNonQuery(command);
        }
       

        public string Delete(KPI KPIs)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_DELETEKPI";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;

            return ExecuteNonQuery(command);
        }

        public string BeforeDelete(KPI KPIs)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_CheckBeforeDeleteKPI";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
            

            return GetSingleData(command);
        }

        

        public IList<KPI> GetAll(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, Employee user, int year,int BusinessGroupID,int yearEnd)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIs";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = user.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;
            command.Parameters.Add("@YearEnd", SqlDbType.VarChar).Value = yearEnd;
            command.Parameters.Add("@BusinessGroupID", SqlDbType.VarChar).Value = BusinessGroupID;

            DataSet dx = GetTable(command);

            List<KPI> KPIs = new List<KPI>();

            if (dx.Tables.Count>0)
            {
                switch (SortDirection)
                {
                    case "asc":
                        switch (SortCol)
                        {
                            case "Description":
                                dx.Tables[0].DefaultView.Sort = "Description ASC";
                                break;
                            case "GroupKPI":
                                dx.Tables[0].DefaultView.Sort = "GroupID ASC";
                                break;
                            case "CategoryKPI":
                                dx.Tables[0].DefaultView.Sort = "CategoryID ASC";
                                break;
                            case "TypeKPI":
                                dx.Tables[0].DefaultView.Sort = "TypeID ASC";
                                break;
                            case "SubTypeKPI":
                                dx.Tables[0].DefaultView.Sort = "SubTypeID ASC";
                                break;
                            case "Periode":
                                dx.Tables[0].DefaultView.Sort = "Periode ASC";
                                break;
                            case "LastModifiedDate":
                                dx.Tables[0].DefaultView.Sort = "LastModifiedDate ASC";
                                break;
                            case "LastModifiedBy":
                                dx.Tables[0].DefaultView.Sort = "LastModifiedBy ASC";
                                break;
                            case "CreateDate":
                                dx.Tables[0].DefaultView.Sort = "CreateDate ASC";
                                break;
                            case "CreateBy":
                                dx.Tables[0].DefaultView.Sort = "CreateBy ASC";
                                break;
                        }
                        break;
                    case "desc":
                        switch (SortCol)
                        {
                            case "Description":
                                dx.Tables[0].DefaultView.Sort = "Description DESC";
                                break;
                            case "GroupKPI":
                                dx.Tables[0].DefaultView.Sort = "GroupID DESC";
                                break;
                            case "CategoryKPI":
                                dx.Tables[0].DefaultView.Sort = "CategoryID DESC";
                                break;
                            case "TypeKPI":
                                dx.Tables[0].DefaultView.Sort = "TypeID DESC";
                                break;
                            case "SubTypeKPI":
                                dx.Tables[0].DefaultView.Sort = "SubTypeID DESC";
                                break;
                            case "Periode":
                                dx.Tables[0].DefaultView.Sort = "Periode DESC";
                                break;
                            case "LastModifiedDate":
                                dx.Tables[0].DefaultView.Sort = "LastModifiedDate DESC";
                                break;
                            case "LastModifiedBy":
                                dx.Tables[0].DefaultView.Sort = "LastModifiedBy DESC";
                                break;
                            case "CreateDate":
                                dx.Tables[0].DefaultView.Sort = "CreateDate DESC";
                                break;
                            case "CreateBy":
                                dx.Tables[0].DefaultView.Sort = "CreateBy DESC";
                                break;
                        }
                        break;
                }
            }

            DataTable dt = new DataTable();
            if(dx.Tables.Count>0)
               dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        KPI p = new KPI();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.Description = dt.Rows[i]["Description"].ToString();
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                        
                        //p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                        //p.CategoryKPI = new CategoryKPIController().GetCategoryKPIByID(Convert.ToInt16(dt.Rows[i]["CategoryID"].ToString()));
                        //p.TypeKPI = new TypeKPIController().GetTypeKPIByID(Convert.ToInt16(dt.Rows[i]["TypeID"].ToString()));
                        //p.SubTypeKPI = new SubTypeKPIController().GetSubTypeKPIByID(Convert.ToInt16(dt.Rows[i]["SubTypeID"].ToString()));


                        p.GroupKPI = new GroupKPI();
                        p.GroupKPI.Description = dx.Tables[0].Rows[i]["DescriptionGroup"].ToString();

                        p.CategoryKPI = new CategoryKPI();
                        p.CategoryKPI.Description = dx.Tables[0].Rows[i]["DescriptionCategory"].ToString();

                        p.TypeKPI = new TypeKPI();
                        p.TypeKPI.Description = dx.Tables[0].Rows[i]["DescriptionType"].ToString();

                        p.SubTypeKPI = new SubTypeKPI();
                        p.SubTypeKPI.Description = dx.Tables[0].Rows[i]["DescriptionSubType"].ToString();

                        
                        p.Year = Convert.ToInt16(dt.Rows[i]["Year"].ToString());
                        p.Periode = dt.Rows[i]["Periode"].ToString();
                        p.Polarization = dt.Rows[i]["Polarization"].ToString();
                        
                        //p.UoM = new UoMController().GetUoMByID(dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString()));
                        //p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString()));
                        //p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString()));

                        p.UoMDetailMax = new UoMDetail();
                        p.UoMDetailMax.Description = dt.Rows[i]["DescriptionUoMDetailMax"].ToString();

                        p.UoMDetailMin = new UoMDetail();
                        p.UoMDetailMin.Description = dt.Rows[i]["DescriptionUoMDetailMin"].ToString();

                        p.TargetDescription = dt.Rows[i]["targetDescription"] == DBNull.Value ? string.Empty : dt.Rows[i]["targetDescription"].ToString();
                        p.ManualCalculation = dt.Rows[i]["ManualCalculation"] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i]["ManualCalculation"].ToString());
                        p.KPICalculationType = dt.Rows[i]["KPICalculationType"] == DBNull.Value ? string.Empty : dt.Rows[i]["KPICalculationType"].ToString();
                        p.Weight = Convert.ToInt16(dt.Rows[i]["Weight"].ToString());
                        
                        //p.KPIParent = new KPIController().GetKPIByID(dt.Rows[i]["KPIParent"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIParent"].ToString()));
                        //p.KPIReplacement = new KPIController().GetKPIByID(dt.Rows[i]["KPIReplacement"]  == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIReplacement"].ToString()));
                        
                        p.ReplacementDate = dt.Rows[i]["ReplacementDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["ReplacementDate"].ToString());
                        p.ReplacementDescription = dt.Rows[i]["ReplacementDescription"] == DBNull.Value ? string.Empty : dt.Rows[i]["ReplacementDescription"].ToString();
                        p.WeightEnableFlag = dt.Rows[i]["WeightEnableFlag"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = dt.Rows[i]["UoMEnableFlag"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["UoMEnableFlag"].ToString());
                        p.KPIDetailInformation = dt.Rows[i]["KPIDetailInformation"] == DBNull.Value ? string.Empty : dt.Rows[i]["KPIDetailInformation"].ToString();
                        p.KPIStrategicObjective = dt.Rows[i]["KPIStrategicObjective"] == DBNull.Value ? string.Empty : dt.Rows[i]["KPIStrategicObjective"].ToString();
                        
                        //p.KPIChilds = new KPIController().GetKPIChilds(new KPI() { ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString()) });

                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"] == DBNull.Value ? string.Empty : dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        p.ScoredBySystem = dt.Rows[i]["ScoredBySystem"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["ScoredBySystem"].ToString());

                        p.Method.MethodName = dt.Rows[i]["MethodName"] == DBNull.Value ? string.Empty : dt.Rows[i]["MethodName"].ToString();
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return KPIs.ToList();
        }

        public IList<KPI> GetAllAdd(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, Employee user, int year, int BusinessGroupID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIsAdd";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = user.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;
            command.Parameters.Add("@BusinessGroupID", SqlDbType.VarChar).Value = BusinessGroupID;

            DataSet dx = GetTable(command);

            List<KPI> KPIs = new List<KPI>();

            if (dx.Tables.Count > 0)
            {
                switch (SortDirection)
                {
                    case "asc":
                        switch (SortCol)
                        {
                            case "Description":
                                dx.Tables[0].DefaultView.Sort = "Description ASC";
                                break;
                            case "GroupKPI":
                                dx.Tables[0].DefaultView.Sort = "GroupID ASC";
                                break;
                            case "CategoryKPI":
                                dx.Tables[0].DefaultView.Sort = "CategoryID ASC";
                                break;
                            case "TypeKPI":
                                dx.Tables[0].DefaultView.Sort = "TypeID ASC";
                                break;
                            case "SubTypeKPI":
                                dx.Tables[0].DefaultView.Sort = "SubTypeID ASC";
                                break;
                            case "Periode":
                                dx.Tables[0].DefaultView.Sort = "Periode ASC";
                                break;
                            case "LastModifiedDate":
                                dx.Tables[0].DefaultView.Sort = "LastModifiedDate ASC";
                                break;
                            case "LastModifiedBy":
                                dx.Tables[0].DefaultView.Sort = "LastModifiedBy ASC";
                                break;
                            case "CreateDate":
                                dx.Tables[0].DefaultView.Sort = "CreateDate ASC";
                                break;
                            case "CreateBy":
                                dx.Tables[0].DefaultView.Sort = "CreateBy ASC";
                                break;
                        }
                        break;
                    case "desc":
                        switch (SortCol)
                        {
                            case "Description":
                                dx.Tables[0].DefaultView.Sort = "Description DESC";
                                break;
                            case "GroupKPI":
                                dx.Tables[0].DefaultView.Sort = "GroupID DESC";
                                break;
                            case "CategoryKPI":
                                dx.Tables[0].DefaultView.Sort = "CategoryID DESC";
                                break;
                            case "TypeKPI":
                                dx.Tables[0].DefaultView.Sort = "TypeID DESC";
                                break;
                            case "SubTypeKPI":
                                dx.Tables[0].DefaultView.Sort = "SubTypeID DESC";
                                break;
                            case "Periode":
                                dx.Tables[0].DefaultView.Sort = "Periode DESC";
                                break;
                            case "LastModifiedDate":
                                dx.Tables[0].DefaultView.Sort = "LastModifiedDate DESC";
                                break;
                            case "LastModifiedBy":
                                dx.Tables[0].DefaultView.Sort = "LastModifiedBy DESC";
                                break;
                            case "CreateDate":
                                dx.Tables[0].DefaultView.Sort = "CreateDate DESC";
                                break;
                            case "CreateBy":
                                dx.Tables[0].DefaultView.Sort = "CreateBy DESC";
                                break;
                        }
                        break;
                }
            }

            DataTable dt = new DataTable();
            if(dx.Tables.Count>0)
             dt =  dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        KPI p = new KPI();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.Description = dt.Rows[i]["Description"].ToString();
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());

                        //p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                        //p.CategoryKPI = new CategoryKPIController().GetCategoryKPIByID(Convert.ToInt16(dt.Rows[i]["CategoryID"].ToString()));
                        //p.TypeKPI = new TypeKPIController().GetTypeKPIByID(Convert.ToInt16(dt.Rows[i]["TypeID"].ToString()));
                        //p.SubTypeKPI = new SubTypeKPIController().GetSubTypeKPIByID(Convert.ToInt16(dt.Rows[i]["SubTypeID"].ToString()));


                        p.GroupKPI = new GroupKPI();
                        p.GroupKPI.Description = dx.Tables[0].Rows[i]["DescriptionGroup"].ToString();

                        p.CategoryKPI = new CategoryKPI();
                        p.CategoryKPI.Description = dx.Tables[0].Rows[i]["DescriptionCategory"].ToString();

                        p.TypeKPI = new TypeKPI();
                        p.TypeKPI.Description = dx.Tables[0].Rows[i]["DescriptionType"].ToString();

                        p.SubTypeKPI = new SubTypeKPI();
                        p.SubTypeKPI.Description = dx.Tables[0].Rows[i]["DescriptionSubType"].ToString();


                        p.Year = Convert.ToInt16(dt.Rows[i]["Year"].ToString());
                        p.Periode = dt.Rows[i]["Periode"].ToString();
                        p.Polarization = dt.Rows[i]["Polarization"].ToString();

                        //p.UoM = new UoMController().GetUoMByID(dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString()));
                        //p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString()));
                        //p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString()));

                        p.UoMDetailMax = new UoMDetail();
                        p.UoMDetailMax.Description = dt.Rows[i]["DescriptionUoMDetailMax"].ToString();

                        p.UoMDetailMin = new UoMDetail();
                        p.UoMDetailMin.Description = dt.Rows[i]["DescriptionUoMDetailMin"].ToString();

                        p.TargetDescription = dt.Rows[i]["targetDescription"].ToString();
                        p.ManualCalculation = Convert.ToInt32(dt.Rows[i]["ManualCalculation"].ToString());
                        p.KPICalculationType = dt.Rows[i]["KPICalculationType"].ToString();
                        p.Weight = Convert.ToInt16(dt.Rows[i]["Weight"].ToString());

                        //p.KPIParent = new KPIController().GetKPIByID(dt.Rows[i]["KPIParent"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIParent"].ToString()));
                        //p.KPIReplacement = new KPIController().GetKPIByID(dt.Rows[i]["KPIReplacement"]  == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIReplacement"].ToString()));

                        p.ReplacementDate = dt.Rows[i]["ReplacementDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["ReplacementDate"].ToString());
                        p.ReplacementDescription = dt.Rows[i]["ReplacementDescription"].ToString();
                        p.WeightEnableFlag = Convert.ToBoolean(dt.Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = Convert.ToBoolean(dt.Rows[i]["UoMEnableFlag"].ToString());
                        p.KPIDetailInformation = dt.Rows[i]["KPIDetailInformation"].ToString();
                        p.KPIStrategicObjective = dt.Rows[i]["KPIStrategicObjective"].ToString();

                        //p.KPIChilds = new KPIController().GetKPIChilds(new KPI() { ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString()) });

                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        p.ScoredBySystem = Convert.ToBoolean(dt.Rows[i]["ScoredBySystem"].ToString());

                        p.Method.MethodName = dt.Rows[i]["MethodName"].ToString();
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return KPIs.ToList();
        }

        public IList<KPI> GetAllPickedKPI(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, Employee user, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllPickedKPIs";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = user.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            DataSet dx = GetTable(command);

            List<KPI> KPIs = new List<KPI>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "Description":
                            dx.Tables[0].DefaultView.Sort = "Description ASC";
                            break;
                        case "GroupKPI":
                            dx.Tables[0].DefaultView.Sort = "GroupID ASC";
                            break;
                        case "CategoryKPI":
                            dx.Tables[0].DefaultView.Sort = "CategoryID ASC";
                            break;
                        case "TypeKPI":
                            dx.Tables[0].DefaultView.Sort = "TypeID ASC";
                            break;
                        case "SubTypeKPI":
                            dx.Tables[0].DefaultView.Sort = "SubTypeID ASC";
                            break;
                        case "Periode":
                            dx.Tables[0].DefaultView.Sort = "Periode ASC";
                            break;
                        case "Weight":
                            dx.Tables[0].DefaultView.Sort = "Weight ASC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMax ASC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMin ASC";
                            break;
                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "Description":
                            dx.Tables[0].DefaultView.Sort = "Description DESC";
                            break;
                        case "GroupKPI":
                            dx.Tables[0].DefaultView.Sort = "GroupID DESC";
                            break;
                        case "CategoryKPI":
                            dx.Tables[0].DefaultView.Sort = "CategoryID DESC";
                            break;
                        case "TypeKPI":
                            dx.Tables[0].DefaultView.Sort = "TypeID DESC";
                            break;
                        case "SubTypeKPI":
                            dx.Tables[0].DefaultView.Sort = "SubTypeID DESC";
                            break;
                        case "Periode":
                            dx.Tables[0].DefaultView.Sort = "Periode DESC";
                            break;
                        case "Weight":
                            dx.Tables[0].DefaultView.Sort = "Weight DESC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMax DESC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMin DESC";
                            break;
                    }
                    break;
            }

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        KPI p = new KPI();
                        p.ID = dt.Rows[i]["ID"]==DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.Description = dt.Rows[i]["Description"]==DBNull.Value?string.Empty : dt.Rows[i]["Description"].ToString();
                        p.isActive = dt.Rows[i]["isActive"]==DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());

                        //p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                        p.GroupKPI = new GroupKPI();
                        p.GroupKPI.Description = dt.Rows[i]["DescriptionGroup"]==DBNull.Value? string.Empty : dt.Rows[i]["DescriptionGroup"].ToString();

                        //p.CategoryKPI = new CategoryKPIController().GetCategoryKPIByID(Convert.ToInt16(dt.Rows[i]["CategoryID"].ToString()));
                        p.CategoryKPI = new CategoryKPI();
                        p.CategoryKPI.Description = dt.Rows[i]["DescriptionCategory"]==DBNull.Value? string.Empty : dt.Rows[i]["DescriptionCategory"].ToString();

                        //p.TypeKPI = new TypeKPIController().GetTypeKPIByID(Convert.ToInt16(dt.Rows[i]["TypeID"].ToString()));
                        p.TypeKPI = new TypeKPI();
                        p.TypeKPI.Description = dt.Rows[i]["DescriptionType"]==DBNull.Value ? string.Empty: dt.Rows[i]["DescriptionType"].ToString();

                        //p.SubTypeKPI = new SubTypeKPIController().GetSubTypeKPIByID(Convert.ToInt16(dt.Rows[i]["SubTypeID"].ToString()));
                        p.SubTypeKPI = new SubTypeKPI();
                        p.SubTypeKPI.Description = dt.Rows[i]["DescriptionSubType"]==DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionSubType"].ToString();
                        
                        p.Year = dt.Rows[i]["Year"] ==DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Year"].ToString());
                        p.Periode = dt.Rows[i]["Periode"]==DBNull.Value?string.Empty : dt.Rows[i]["Periode"].ToString();
                        p.Polarization = dt.Rows[i]["Polarization"] == DBNull.Value ? string.Empty : dt.Rows[i]["Polarization"].ToString();

                        p.UoMDetailMax = new UoMDetail();
                        p.UoMDetailMax.Description = dt.Rows[i]["DescriptionUoMDetailMax"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionUoMDetailMax"].ToString();

                        p.UoMDetailMin = new UoMDetail();
                        p.UoMDetailMin.Description = dt.Rows[i]["DescriptionUoMDetailMin"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionUoMDetailMin"].ToString();
                        
                        //p.UoM = new UoMController().GetUoMByID(dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString()));
                        //p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString()));
                        //p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString()));

                        p.TargetDescription = dt.Rows[i]["targetDescription"] == DBNull.Value ? string.Empty : dt.Rows[i]["targetDescription"].ToString();
                        p.ManualCalculation = dt.Rows[i]["ManualCalculation"] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i]["ManualCalculation"].ToString());
                        p.KPICalculationType = dt.Rows[i]["KPICalculationType"] == DBNull.Value ? string.Empty : dt.Rows[i]["KPICalculationType"].ToString();
                        p.Weight = dt.Rows[i]["Weight"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Weight"].ToString());
                        
                        //p.KPIParent = new KPIController().GetKPIByID(dt.Rows[i]["KPIParent"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIParent"].ToString()));
                        //p.KPIReplacement = new KPIController().GetKPIByID(dt.Rows[i]["KPIReplacement"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIReplacement"].ToString()));
                        
                        p.ReplacementDate = dt.Rows[i]["ReplacementDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["ReplacementDate"].ToString());
                        p.ReplacementDescription = dt.Rows[i]["targetDescription"] == DBNull.Value ? string.Empty : dt.Rows[i]["ReplacementDescription"].ToString();
                        p.WeightEnableFlag = dt.Rows[i]["WeightEnableFlag"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = dt.Rows[i]["UoMEnableFlag"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["UoMEnableFlag"].ToString());
                        p.KPIDetailInformation = dt.Rows[i]["KPIDetailInformation"] == DBNull.Value ? string.Empty : dt.Rows[i]["KPIDetailInformation"].ToString();
                        p.KPIStrategicObjective = dt.Rows[i]["KPIStrategicObjective"] == DBNull.Value ? string.Empty : dt.Rows[i]["KPIStrategicObjective"].ToString();
                        //p.KPIChilds = new KPIController().GetKPIChilds(new KPI() { ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString()) });
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"] == DBNull.Value ? string.Empty : dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"] == DBNull.Value ? string.Empty : dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate =  dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        p.ScoredBySystem = dt.Rows[i]["ScoredBySystem"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["ScoredBySystem"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return KPIs.ToList();
        }

        public IList<KPI> GetAllBreakDownKPI(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, Employee user, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllBreakDownKPIs";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = user.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            DataSet dx = GetTable(command);

            List<KPI> KPIs = new List<KPI>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "Description":
                            dx.Tables[0].DefaultView.Sort = "Description ASC";
                            break;
                        case "GroupKPI":
                            dx.Tables[0].DefaultView.Sort = "GroupID ASC";
                            break;
                        case "CategoryKPI":
                            dx.Tables[0].DefaultView.Sort = "CategoryID ASC";
                            break;
                        case "TypeKPI":
                            dx.Tables[0].DefaultView.Sort = "TypeID ASC";
                            break;
                        case "SubTypeKPI":
                            dx.Tables[0].DefaultView.Sort = "SubTypeID ASC";
                            break;
                        case "Periode":
                            dx.Tables[0].DefaultView.Sort = "Periode ASC";
                            break;
                        case "Weight":
                            dx.Tables[0].DefaultView.Sort = "Weight ASC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMax ASC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMin ASC";
                            break;
                        case "KPIParent":
                            dx.Tables[0].DefaultView.Sort = "Description ASC";
                            break;

                            
                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "Description":
                            dx.Tables[0].DefaultView.Sort = "Description DESC";
                            break;
                        case "GroupKPI":
                            dx.Tables[0].DefaultView.Sort = "GroupID DESC";
                            break;
                        case "CategoryKPI":
                            dx.Tables[0].DefaultView.Sort = "CategoryID DESC";
                            break;
                        case "TypeKPI":
                            dx.Tables[0].DefaultView.Sort = "TypeID DESC";
                            break;
                        case "SubTypeKPI":
                            dx.Tables[0].DefaultView.Sort = "SubTypeID DESC";
                            break;
                        case "Periode":
                            dx.Tables[0].DefaultView.Sort = "Periode DESC";
                            break;
                        case "Weight":
                            dx.Tables[0].DefaultView.Sort = "Weight DESC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMax DESC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMin DESC";
                            break;
                        case "KPIParent":
                            dx.Tables[0].DefaultView.Sort = "Description DESC";
                            break;
                    }
                    break;
            }

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        KPI p = new KPI();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.Description = dt.Rows[i]["Description"].ToString();
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());

                        //p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                        p.GroupKPI = new GroupKPI();
                        p.GroupKPI.Description = dt.Rows[i]["DescriptionGroup"].ToString();

                        //p.CategoryKPI = new CategoryKPIController().GetCategoryKPIByID(Convert.ToInt16(dt.Rows[i]["CategoryID"].ToString()));
                        p.CategoryKPI = new CategoryKPI();
                        p.CategoryKPI.Description = dt.Rows[i]["DescriptionCategory"].ToString();

                        //p.TypeKPI = new TypeKPIController().GetTypeKPIByID(Convert.ToInt16(dt.Rows[i]["TypeID"].ToString()));
                        p.TypeKPI = new TypeKPI();
                        p.TypeKPI.Description = dt.Rows[i]["DescriptionType"].ToString();

                        //p.SubTypeKPI = new SubTypeKPIController().GetSubTypeKPIByID(Convert.ToInt16(dt.Rows[i]["SubTypeID"].ToString()));
                        p.SubTypeKPI = new SubTypeKPI();
                        p.SubTypeKPI.Description = dt.Rows[i]["DescriptionSubType"].ToString();
                        
                        p.Year = Convert.ToInt16(dt.Rows[i]["Year"].ToString());
                        p.Periode = dt.Rows[i]["Periode"].ToString();
                        p.Polarization = dt.Rows[i]["Polarization"].ToString();
                        
                        //p.UoM = new UoMController().GetUoMByID(dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString()));
                        //p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString()));
                        //p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString()));

                        p.UoMDetailMax = new UoMDetail();
                        p.UoMDetailMax.Description = dt.Rows[i]["DescriptionUoMDetailMax"].ToString();

                        p.UoMDetailMin = new UoMDetail();
                        p.UoMDetailMin.Description = dt.Rows[i]["DescriptionUoMDetailMin"].ToString();

                        p.TargetDescription = dt.Rows[i]["targetDescription"].ToString();
                        p.ManualCalculation = Convert.ToInt32(dt.Rows[i]["ManualCalculation"].ToString());
                        p.KPICalculationType = dt.Rows[i]["KPICalculationType"].ToString();
                        p.Weight = Convert.ToInt16(dt.Rows[i]["Weight"].ToString());
                        
                        //p.KPIParent = new KPIController().GetKPIByID(dt.Rows[i]["KPIParent"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIParent"].ToString()));
                        p.KPIParent = new KPI();
                        p.KPIParent.Description = dt.Rows[i]["DescriptionKPIParent"].ToString();
                        //p.KPIReplacement = new KPIController().GetKPIByID(dt.Rows[i]["KPIReplacement"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIReplacement"].ToString()));
                        
                        p.ReplacementDate = dt.Rows[i]["ReplacementDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["ReplacementDate"].ToString());
                        p.ReplacementDescription = dt.Rows[i]["ReplacementDescription"].ToString();
                        p.WeightEnableFlag = Convert.ToBoolean(dt.Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = Convert.ToBoolean(dt.Rows[i]["UoMEnableFlag"].ToString());
                        p.KPIDetailInformation = dt.Rows[i]["KPIDetailInformation"].ToString();
                        p.KPIStrategicObjective = dt.Rows[i]["KPIStrategicObjective"].ToString();
                        p.KPIChilds = new KPIController().GetKPIChilds(new KPI() { ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString()) });
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        p.ScoredBySystem = Convert.ToBoolean(dt.Rows[i]["ScoredBySystem"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return KPIs.ToList();
        }

        public string GetAllKPIToBeAssignedCount(string sSearch, Employee user, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIToBeAssigned_Count";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = user.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;


            return GetSingleData(command);
        }

        public string GetAllKPIToBeAssignedCountNon(string sSearch, Employee user, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIToBeAssigned_CountNonMandatory";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = user.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;


            return GetSingleData(command);
        }

        public IList<KPI> GetAllKPIToBeAssigned(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, Employee user, int year)//,bool Mandatory)
        {
            PageNumber = PageNumber + 1;
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIToBeAssigned";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = user.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            command.Parameters.Add("@startRow", SqlDbType.VarChar).Value = PageNumber;
            command.Parameters.Add("@endRow", SqlDbType.VarChar).Value = Datalength;
            command.Parameters.Add("@sortName", SqlDbType.VarChar).Value = SortCol;
            command.Parameters.Add("@sortOrder", SqlDbType.VarChar).Value = SortDirection;
            //command.Parameters.Add("@Mandatory", SqlDbType.VarChar).Value = Mandatory;

            DataSet dx = GetTable(command);

            List<KPI> KPIs = new List<KPI>();

            //switch (SortDirection)
            //{
            //    case "asc":
            //        switch (SortCol)
            //        {
            //            case "Description":
            //                dx.Tables[0].DefaultView.Sort = "Description ASC";
            //                break;
            //            case "GroupKPI":
            //                dx.Tables[0].DefaultView.Sort = "GroupID ASC";
            //                break;
            //            case "CategoryKPI":
            //                dx.Tables[0].DefaultView.Sort = "CategoryID ASC";
            //                break;
            //            case "TypeKPI":
            //                dx.Tables[0].DefaultView.Sort = "TypeID ASC";
            //                break;
            //            case "SubTypeKPI":
            //                dx.Tables[0].DefaultView.Sort = "SubTypeID ASC";
            //                break;
            //            case "Periode":
            //                dx.Tables[0].DefaultView.Sort = "Periode ASC";
            //                break;
            //            case "LastModifiedDate":
            //                dx.Tables[0].DefaultView.Sort = "LastModifiedDate ASC";
            //                break;
            //            case "LastModifiedBy":
            //                dx.Tables[0].DefaultView.Sort = "LastModifiedBy ASC";
            //                break;
            //            case "CreateDate":
            //                dx.Tables[0].DefaultView.Sort = "CreateDate ASC";
            //                break;
            //            case "CreateBy":
            //                dx.Tables[0].DefaultView.Sort = "CreateBy ASC";
            //                break;
            //        }
            //        break;
            //    case "desc":
            //        switch (SortCol)
            //        {
            //            case "Description":
            //                dx.Tables[0].DefaultView.Sort = "Description DESC";
            //                break;
            //            case "GroupKPI":
            //                dx.Tables[0].DefaultView.Sort = "GroupID DESC";
            //                break;
            //            case "CategoryKPI":
            //                dx.Tables[0].DefaultView.Sort = "CategoryID DESC";
            //                break;
            //            case "TypeKPI":
            //                dx.Tables[0].DefaultView.Sort = "TypeID DESC";
            //                break;
            //            case "SubTypeKPI":
            //                dx.Tables[0].DefaultView.Sort = "SubTypeID DESC";
            //                break;
            //            case "Periode":
            //                dx.Tables[0].DefaultView.Sort = "Periode DESC";
            //                break;
            //            case "LastModifiedDate":
            //                dx.Tables[0].DefaultView.Sort = "LastModifiedDate DESC";
            //                break;
            //            case "LastModifiedBy":
            //                dx.Tables[0].DefaultView.Sort = "LastModifiedBy DESC";
            //                break;
            //            case "CreateDate":
            //                dx.Tables[0].DefaultView.Sort = "CreateDate DESC";
            //                break;
            //            case "CreateBy":
            //                dx.Tables[0].DefaultView.Sort = "CreateBy DESC";
            //                break;
            //        }
            //        break;
            //}

            DataTable dt = dx.Tables[0];//.DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //if (i < dt.Rows.Count)
                    //{
                        KPI p = new KPI();
                        p.ID = dt.Rows[i]["ID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.Description = dt.Rows[i]["Description"] == DBNull.Value ? string.Empty : dt.Rows[i]["Description"].ToString();
                        p.isActive = dt.Rows[i]["isActive"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());

                        //p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                        //p.CategoryKPI = new CategoryKPIController().GetCategoryKPIByID(Convert.ToInt16(dt.Rows[i]["CategoryID"].ToString()));
                        //p.TypeKPI = new TypeKPIController().GetTypeKPIByID(Convert.ToInt16(dt.Rows[i]["TypeID"].ToString()));
                        //p.SubTypeKPI = new SubTypeKPIController().GetSubTypeKPIByID(Convert.ToInt16(dt.Rows[i]["SubTypeID"].ToString()));

                        p.GroupKPI = new GroupKPI();
                        p.GroupKPI.Description = dt.Rows[i]["DescriptionGroup"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionGroup"].ToString();

                        p.CategoryKPI = new CategoryKPI();
                        p.CategoryKPI.Description = dt.Rows[i]["DescriptionCategory"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionCategory"].ToString();

                        p.TypeKPI = new TypeKPI();
                        p.TypeKPI.Description = dt.Rows[i]["DescriptionType"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionType"].ToString();

                        p.SubTypeKPI = new SubTypeKPI();
                        p.SubTypeKPI.Description = dt.Rows[i]["DescriptionSubType"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionSubType"].ToString();


                        p.Year = dt.Rows[i]["Year"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Year"].ToString());
                        p.Periode = dt.Rows[i]["Periode"] == DBNull.Value ? string.Empty : dt.Rows[i]["Periode"].ToString();
                        p.Polarization = dt.Rows[i]["Polarization"] == DBNull.Value ? string.Empty : dt.Rows[i]["Polarization"].ToString();
                        
                        //p.UoM = new UoMController().GetUoMByID(dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString()));
                        //p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString()));
                        //p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString()));

                        p.UoMDetailMax = new UoMDetail();
                        p.UoMDetailMax.Description = dt.Rows[i]["DescriptionUoMDetailMax"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionUoMDetailMax"].ToString();

                        p.UoMDetailMin = new UoMDetail();
                        p.UoMDetailMin.Description = dt.Rows[i]["DescriptionUoMDetailMin"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionUoMDetailMin"].ToString();

                        p.TargetDescription = dt.Rows[i]["targetDescription"] == DBNull.Value ? string.Empty : dt.Rows[i]["targetDescription"].ToString();
                        p.ManualCalculation = dt.Rows[i]["ManualCalculation"] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i]["ManualCalculation"].ToString());
                        p.KPICalculationType = dt.Rows[i]["KPICalculationType"] == DBNull.Value ? string.Empty : dt.Rows[i]["KPICalculationType"].ToString();
                        p.Weight = dt.Rows[i]["Weight"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Weight"].ToString());

                        //p.KPIParent = new KPIController().GetKPIByID(dt.Rows[i]["KPIParent"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIParent"].ToString()));
                        //p.KPIReplacement = new KPIController().GetKPIByID(dt.Rows[i]["KPIReplacement"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIReplacement"].ToString()));
                        
                        p.ReplacementDate = dt.Rows[i]["ReplacementDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["ReplacementDate"].ToString());
                        p.ReplacementDescription = dt.Rows[i]["ReplacementDescription"] == DBNull.Value ? string.Empty : dt.Rows[i]["ReplacementDescription"].ToString();
                        p.WeightEnableFlag = dt.Rows[i]["WeightEnableFlag"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = dt.Rows[i]["UoMEnableFlag"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["UoMEnableFlag"].ToString());
                        p.KPIDetailInformation = dt.Rows[i]["KPIDetailInformation"] == DBNull.Value ? string.Empty : dt.Rows[i]["KPIDetailInformation"].ToString();
                        p.KPIStrategicObjective = dt.Rows[i]["KPIStrategicObjective"] == DBNull.Value ? string.Empty : dt.Rows[i]["KPIStrategicObjective"].ToString();

                        //p.KPIChilds = new KPIController().GetKPIChilds(new KPI() { ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString()) });

                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"] == DBNull.Value ? string.Empty : dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"] == DBNull.Value ? string.Empty : dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        p.ScoredBySystem = dt.Rows[i]["ScoredBySystem"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["ScoredBySystem"].ToString());
                        KPIs.Add(p);
                    //}
                    //else
                    //{
                    //    break;
                    //}

                }
            }

            TotalData = dt.Rows.Count;

            return KPIs.ToList();
        }

        public IList<KPI> GetAllKPIToBeAssignedNonMandatory(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, Employee user, int year)
        {
            PageNumber = PageNumber + 1;
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIToBeAssigned_NonMandatory";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = user.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            command.Parameters.Add("@startRow", SqlDbType.VarChar).Value = PageNumber;
            command.Parameters.Add("@endRow", SqlDbType.VarChar).Value = Datalength;
            command.Parameters.Add("@sortName", SqlDbType.VarChar).Value = SortCol;
            command.Parameters.Add("@sortOrder", SqlDbType.VarChar).Value = SortDirection;

            DataSet dx = GetTable(command);

            List<KPI> KPIs = new List<KPI>();

            //switch (SortDirection)
            //{
            //    case "asc":
            //        switch (SortCol)
            //        {
            //            case "Description":
            //                dx.Tables[0].DefaultView.Sort = "Description ASC";
            //                break;
            //            case "GroupKPI":
            //                dx.Tables[0].DefaultView.Sort = "GroupID ASC";
            //                break;
            //            case "CategoryKPI":
            //                dx.Tables[0].DefaultView.Sort = "CategoryID ASC";
            //                break;
            //            case "TypeKPI":
            //                dx.Tables[0].DefaultView.Sort = "TypeID ASC";
            //                break;
            //            case "SubTypeKPI":
            //                dx.Tables[0].DefaultView.Sort = "SubTypeID ASC";
            //                break;
            //            case "Periode":
            //                dx.Tables[0].DefaultView.Sort = "Periode ASC";
            //                break;
            //            case "LastModifiedDate":
            //                dx.Tables[0].DefaultView.Sort = "LastModifiedDate ASC";
            //                break;
            //            case "LastModifiedBy":
            //                dx.Tables[0].DefaultView.Sort = "LastModifiedBy ASC";
            //                break;
            //            case "CreateDate":
            //                dx.Tables[0].DefaultView.Sort = "CreateDate ASC";
            //                break;
            //            case "CreateBy":
            //                dx.Tables[0].DefaultView.Sort = "CreateBy ASC";
            //                break;
            //        }
            //        break;
            //    case "desc":
            //        switch (SortCol)
            //        {
            //            case "Description":
            //                dx.Tables[0].DefaultView.Sort = "Description DESC";
            //                break;
            //            case "GroupKPI":
            //                dx.Tables[0].DefaultView.Sort = "GroupID DESC";
            //                break;
            //            case "CategoryKPI":
            //                dx.Tables[0].DefaultView.Sort = "CategoryID DESC";
            //                break;
            //            case "TypeKPI":
            //                dx.Tables[0].DefaultView.Sort = "TypeID DESC";
            //                break;
            //            case "SubTypeKPI":
            //                dx.Tables[0].DefaultView.Sort = "SubTypeID DESC";
            //                break;
            //            case "Periode":
            //                dx.Tables[0].DefaultView.Sort = "Periode DESC";
            //                break;
            //            case "LastModifiedDate":
            //                dx.Tables[0].DefaultView.Sort = "LastModifiedDate DESC";
            //                break;
            //            case "LastModifiedBy":
            //                dx.Tables[0].DefaultView.Sort = "LastModifiedBy DESC";
            //                break;
            //            case "CreateDate":
            //                dx.Tables[0].DefaultView.Sort = "CreateDate DESC";
            //                break;
            //            case "CreateBy":
            //                dx.Tables[0].DefaultView.Sort = "CreateBy DESC";
            //                break;
            //        }
            //        break;
            //}

            DataTable dt = dx.Tables[0];//.DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //if (i < dt.Rows.Count)
                    //{
                    KPI p = new KPI();
                    p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                    p.Description = dt.Rows[i]["Description"].ToString();
                    p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                    p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                    p.CategoryKPI = new CategoryKPIController().GetCategoryKPIByID(Convert.ToInt16(dt.Rows[i]["CategoryID"].ToString()));
                    p.TypeKPI = new TypeKPIController().GetTypeKPIByID(Convert.ToInt16(dt.Rows[i]["TypeID"].ToString()));
                    p.SubTypeKPI = new SubTypeKPIController().GetSubTypeKPIByID(Convert.ToInt16(dt.Rows[i]["SubTypeID"].ToString()));
                    p.Year = Convert.ToInt16(dt.Rows[i]["Year"].ToString());
                    p.Periode = dt.Rows[i]["Periode"].ToString();
                    p.Polarization = dt.Rows[i]["Polarization"].ToString();
                    p.UoM = new UoMController().GetUoMByID(dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString()));
                    p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString()));
                    p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString()));
                    p.TargetDescription = dt.Rows[i]["targetDescription"].ToString();
                    p.ManualCalculation = Convert.ToInt32(dt.Rows[i]["ManualCalculation"].ToString());
                    p.KPICalculationType = dt.Rows[i]["KPICalculationType"].ToString();
                    p.Weight = Convert.ToInt16(dt.Rows[i]["Weight"].ToString());
                    p.KPIParent = new KPIController().GetKPIByID(dt.Rows[i]["KPIParent"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIParent"].ToString()));
                    p.KPIReplacement = new KPIController().GetKPIByID(dt.Rows[i]["KPIReplacement"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIReplacement"].ToString()));
                    p.ReplacementDate = dt.Rows[i]["ReplacementDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["ReplacementDate"].ToString());
                    p.ReplacementDescription = dt.Rows[i]["ReplacementDescription"].ToString();
                    p.WeightEnableFlag = Convert.ToBoolean(dt.Rows[i]["WeightEnableFlag"].ToString());
                    p.UoMEnableFlag = Convert.ToBoolean(dt.Rows[i]["UoMEnableFlag"].ToString());
                    p.KPIDetailInformation = dt.Rows[i]["KPIDetailInformation"].ToString();
                    p.KPIStrategicObjective = dt.Rows[i]["KPIStrategicObjective"].ToString();
                    p.KPIChilds = new KPIController().GetKPIChilds(new KPI() { ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString()) });
                    p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                    p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                    p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                    p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                    p.ScoredBySystem = Convert.ToBoolean(dt.Rows[i]["ScoredBySystem"].ToString());
                    KPIs.Add(p);
                    //}
                    //else
                    //{
                    //    break;
                    //}

                }
            }

            TotalData = dt.Rows.Count;

            return KPIs.ToList();
        }

        public IList<KPI> GetAvailableKPI(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, Employee user, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAvailableKPIs";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = user.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            DataSet dx = GetTable(command);

            List<KPI> KPIs = new List<KPI>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "Description":
                            dx.Tables[0].DefaultView.Sort = "Description ASC";
                            break;
                        case "GroupKPI":
                            dx.Tables[0].DefaultView.Sort = "GroupID ASC";
                            break;
                        case "CategoryKPI":
                            dx.Tables[0].DefaultView.Sort = "CategoryID ASC";
                            break;
                        case "TypeKPI":
                            dx.Tables[0].DefaultView.Sort = "TypeID ASC";
                            break;
                        case "SubTypeKPI":
                            dx.Tables[0].DefaultView.Sort = "SubTypeID ASC";
                            break;
                        case "Periode":
                            dx.Tables[0].DefaultView.Sort = "Periode ASC";
                            break;
                        case "Weight":
                            dx.Tables[0].DefaultView.Sort = "Weight ASC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMax ASC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMin ASC";
                            break;
                        //case "CreateBy":
                        //    dx.Tables[0].DefaultView.Sort = "CreateBy ASC";
                        //    break;
                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "Description":
                            dx.Tables[0].DefaultView.Sort = "Description DESC";
                            break;
                        case "GroupKPI":
                            dx.Tables[0].DefaultView.Sort = "GroupID DESC";
                            break;
                        case "CategoryKPI":
                            dx.Tables[0].DefaultView.Sort = "CategoryID DESC";
                            break;
                        case "TypeKPI":
                            dx.Tables[0].DefaultView.Sort = "TypeID DESC";
                            break;
                        case "SubTypeKPI":
                            dx.Tables[0].DefaultView.Sort = "SubTypeID DESC";
                            break;
                        case "Periode":
                            dx.Tables[0].DefaultView.Sort = "Periode DESC";
                            break;
                        case "Weight":
                            dx.Tables[0].DefaultView.Sort = "Weight DESC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMax DESC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMin DESC";
                            break;
                        //case "CreateBy":
                        //    dx.Tables[0].DefaultView.Sort = "CreateBy DESC";
                        //    break;
                    }
                    break;
            }

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        KPI p = new KPI();
                        p.ID = dt.Rows[i]["ID"] == DBNull.Value?0: Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.Description = dt.Rows[i]["Description"]==DBNull.Value ? string.Empty : dt.Rows[i]["Description"].ToString();
                        p.isActive = dt.Rows[i]["isActive"]==DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());

                        //p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                        p.GroupKPI = new GroupKPI();
                        p.GroupKPI.Description = dt.Rows[i]["DescriptionGroup"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionGroup"].ToString();

                        //p.CategoryKPI = new CategoryKPIController().GetCategoryKPIByID(Convert.ToInt16(dt.Rows[i]["CategoryID"].ToString()));
                        p.CategoryKPI = new CategoryKPI();
                        p.CategoryKPI.Description = dt.Rows[i]["DescriptionCategory"]==DBNull.Value?string.Empty :  dt.Rows[i]["DescriptionCategory"].ToString();

                        //p.TypeKPI = new TypeKPIController().GetTypeKPIByID(Convert.ToInt16(dt.Rows[i]["TypeID"].ToString()));
                        p.TypeKPI = new TypeKPI();
                        p.TypeKPI.Description = dt.Rows[i]["DescriptionType"]==DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionType"].ToString();

                        //p.SubTypeKPI = new SubTypeKPIController().GetSubTypeKPIByID(Convert.ToInt16(dt.Rows[i]["SubTypeID"].ToString()));
                        p.SubTypeKPI = new SubTypeKPI();
                        p.SubTypeKPI.Description = dt.Rows[i]["DescriptionSubType"]==DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionSubType"].ToString();
                        
                        p.Year = dt.Rows[i]["Year"]==DBNull.Value ?0: Convert.ToInt16(dt.Rows[i]["Year"].ToString());
                        p.Periode = dt.Rows[i]["Periode"]==DBNull.Value ? string.Empty: dt.Rows[i]["Periode"].ToString();
                        p.Polarization = dt.Rows[i]["Polarization"]==DBNull.Value ?string.Empty : dt.Rows[i]["Polarization"].ToString();
                        
                        //p.UoM = new UoMController().GetUoMByID(dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString()));
                        //p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString()));
                        //p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString()));

                        p.UoMDetailMax = new UoMDetail();
                        p.UoMDetailMax.Description = dt.Rows[i]["DescriptionUoMDetailMax"]==DBNull.Value?string.Empty : dt.Rows[i]["DescriptionUoMDetailMax"].ToString();

                        p.UoMDetailMin = new UoMDetail();
                        p.UoMDetailMin.Description = dt.Rows[i]["DescriptionUoMDetailMin"]==DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionUoMDetailMin"].ToString();

                        p.TargetDescription = dt.Rows[i]["targetDescription"] == DBNull.Value ? string.Empty : dt.Rows[i]["targetDescription"].ToString();
                        p.ManualCalculation = dt.Rows[i]["ManualCalculation"]==DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i]["ManualCalculation"].ToString());
                        p.KPICalculationType = dt.Rows[i]["KPICalculationType"] == DBNull.Value ? string.Empty : dt.Rows[i]["KPICalculationType"].ToString();
                        p.Weight = dt.Rows[i]["Weight"]==DBNull.Value?0: Convert.ToInt16(dt.Rows[i]["Weight"].ToString());
                       
                        //p.KPIParent = new KPIController().GetKPIByID(dt.Rows[i]["KPIParent"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIParent"].ToString()));
                        //p.KPIReplacement = new KPIController().GetKPIByID(dt.Rows[i]["KPIReplacement"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIReplacement"].ToString()));
                        
                        p.ReplacementDate = dt.Rows[i]["ReplacementDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["ReplacementDate"].ToString());
                        p.ReplacementDescription = dt.Rows[i]["ReplacementDescription"]==DBNull.Value ? string.Empty : dt.Rows[i]["ReplacementDescription"].ToString();
                        p.WeightEnableFlag = dt.Rows[i]["WeightEnableFlag"]==DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = dt.Rows[i]["UoMEnableFlag"]==DBNull.Value?false :  Convert.ToBoolean(dt.Rows[i]["UoMEnableFlag"].ToString());
                        p.KPIDetailInformation = dt.Rows[i]["KPIDetailInformation"]==DBNull.Value ? string.Empty : dt.Rows[i]["KPIDetailInformation"].ToString();
                        p.KPIStrategicObjective = dt.Rows[i]["KPIStrategicObjective"]==DBNull.Value ? string.Empty : dt.Rows[i]["KPIStrategicObjective"].ToString();
                        //p.KPIChilds = new KPIController().GetKPIChilds(new KPI() { ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString()) });
                        
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"]==DBNull.Value?string.Empty : dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"]==DBNull.Value ? string.Empty : dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        
                        p.ScoredBySystem = dt.Rows[i]["ScoredBySystem"]==DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["ScoredBySystem"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return KPIs.ToList();
        }

        public IList<KPI> GetAvailableKPIUnSign(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, Employee user, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAvailableKPIs_UnSign";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = user.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            DataSet dx = GetTable(command);

            List<KPI> KPIs = new List<KPI>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "Description":
                            dx.Tables[0].DefaultView.Sort = "Description ASC";
                            break;
                        case "GroupKPI":
                            dx.Tables[0].DefaultView.Sort = "GroupID ASC";
                            break;
                        case "CategoryKPI":
                            dx.Tables[0].DefaultView.Sort = "CategoryID ASC";
                            break;
                        case "TypeKPI":
                            dx.Tables[0].DefaultView.Sort = "TypeID ASC";
                            break;
                        case "SubTypeKPI":
                            dx.Tables[0].DefaultView.Sort = "SubTypeID ASC";
                            break;
                        case "Periode":
                            dx.Tables[0].DefaultView.Sort = "Periode ASC";
                            break;
                        case "Weight":
                            dx.Tables[0].DefaultView.Sort = "Weight ASC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMax ASC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMin ASC";
                            break;
                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "Description":
                            dx.Tables[0].DefaultView.Sort = "Description DESC";
                            break;
                        case "GroupKPI":
                            dx.Tables[0].DefaultView.Sort = "GroupID DESC";
                            break;
                        case "CategoryKPI":
                            dx.Tables[0].DefaultView.Sort = "CategoryID DESC";
                            break;
                        case "TypeKPI":
                            dx.Tables[0].DefaultView.Sort = "TypeID DESC";
                            break;
                        case "SubTypeKPI":
                            dx.Tables[0].DefaultView.Sort = "SubTypeID DESC";
                            break;
                        case "Periode":
                            dx.Tables[0].DefaultView.Sort = "Periode DESC";
                            break;
                        case "Weight":
                            dx.Tables[0].DefaultView.Sort = "Weight DESC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMax DESC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMin DESC";
                            break;
                            
                    }
                    break;
            }

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        KPI p = new KPI();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.Description = dt.Rows[i]["Description"].ToString();
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                        
                        //p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                        p.GroupKPI = new GroupKPI();
                        p.GroupKPI.Description = dt.Rows[i]["DescriptionGroup"].ToString();

                        //p.CategoryKPI = new CategoryKPIController().GetCategoryKPIByID(Convert.ToInt16(dt.Rows[i]["CategoryID"].ToString()));
                        p.CategoryKPI = new CategoryKPI();
                        p.CategoryKPI.Description = dt.Rows[i]["DescriptionCategory"].ToString();

                        //p.TypeKPI = new TypeKPIController().GetTypeKPIByID(Convert.ToInt16(dt.Rows[i]["TypeID"].ToString()));
                        p.TypeKPI = new TypeKPI();
                        p.TypeKPI.Description = dt.Rows[i]["DescriptionType"].ToString();

                        //p.SubTypeKPI = new SubTypeKPIController().GetSubTypeKPIByID(Convert.ToInt16(dt.Rows[i]["SubTypeID"].ToString()));
                        p.SubTypeKPI = new SubTypeKPI();
                        p.SubTypeKPI.Description = dt.Rows[i]["DescriptionSubType"].ToString();
                        
                        p.Year = Convert.ToInt16(dt.Rows[i]["Year"].ToString());
                        p.Periode = dt.Rows[i]["Periode"].ToString();
                        p.Polarization = dt.Rows[i]["Polarization"].ToString();
                        
                        //p.UoM = new UoMController().GetUoMByID(dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString()));
                        //p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString()));
                        //p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString()));


                        p.UoMDetailMax = new UoMDetail();
                        p.UoMDetailMax.Description = dt.Rows[i]["DescriptionUoMDetailMax"].ToString();

                        p.UoMDetailMin = new UoMDetail();
                        p.UoMDetailMin.Description = dt.Rows[i]["DescriptionUoMDetailMin"].ToString();


                        p.TargetDescription = dt.Rows[i]["targetDescription"].ToString();
                        p.ManualCalculation = Convert.ToInt32(dt.Rows[i]["ManualCalculation"].ToString());
                        p.KPICalculationType = dt.Rows[i]["KPICalculationType"].ToString();
                        p.Weight = Convert.ToInt16(dt.Rows[i]["Weight"].ToString());
                        
                        //p.KPIParent = new KPIController().GetKPIByID(dt.Rows[i]["KPIParent"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIParent"].ToString()));
                        //p.KPIReplacement = new KPIController().GetKPIByID(dt.Rows[i]["KPIReplacement"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIReplacement"].ToString()));
                        
                        p.ReplacementDate = dt.Rows[i]["ReplacementDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["ReplacementDate"].ToString());
                        p.ReplacementDescription = dt.Rows[i]["ReplacementDescription"].ToString();
                        p.WeightEnableFlag = Convert.ToBoolean(dt.Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = Convert.ToBoolean(dt.Rows[i]["UoMEnableFlag"].ToString());
                        p.KPIDetailInformation = dt.Rows[i]["KPIDetailInformation"].ToString();
                        p.KPIStrategicObjective = dt.Rows[i]["KPIStrategicObjective"].ToString();
                        //p.KPIChilds = new KPIController().GetKPIChilds(new KPI() { ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString()) });
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        p.ScoredBySystem = Convert.ToBoolean(dt.Rows[i]["ScoredBySystem"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return KPIs.ToList();
        }

        public IList<KPI> GetKPIBreakDownExpand(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, Employee user, KPI Parent)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIBreakDownExpand";
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = user.ID;
            command.Parameters.Add("@KPIPARENTID", SqlDbType.VarChar).Value = Parent.ID;

            DataSet dx = GetTable(command);

            List<KPI> KPIs = new List<KPI>();

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        KPI p = new KPI();
                        p.ID = dt.Rows[i]["ID"]==DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.Description = dt.Rows[i]["Description"]==DBNull.Value?string.Empty : dt.Rows[i]["Description"].ToString();
                        p.isActive = dt.Rows[i]["isActive"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());

                        //p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                        p.GroupKPI = new GroupKPI();
                        p.GroupKPI.Description = dt.Rows[i]["DescriptionGroup"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionGroup"].ToString();

                        //p.CategoryKPI = new CategoryKPIController().GetCategoryKPIByID(Convert.ToInt16(dt.Rows[i]["CategoryID"].ToString()));
                        p.CategoryKPI = new CategoryKPI();
                        p.CategoryKPI.Description = dt.Rows[i]["DescriptionCategory"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionCategory"].ToString();

                        //p.TypeKPI = new TypeKPIController().GetTypeKPIByID(Convert.ToInt16(dt.Rows[i]["TypeID"].ToString()));
                        p.TypeKPI = new TypeKPI();
                        p.TypeKPI.Description = dt.Rows[i]["DescriptionType"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionType"].ToString();

                        //p.SubTypeKPI = new SubTypeKPIController().GetSubTypeKPIByID(Convert.ToInt16(dt.Rows[i]["SubTypeID"].ToString()));
                        p.SubTypeKPI = new SubTypeKPI();
                        p.SubTypeKPI.Description = dt.Rows[i]["DescriptionSubType"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionSubType"].ToString();


                        p.Year = dt.Rows[i]["Year"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Year"].ToString());
                        p.Periode = dt.Rows[i]["Periode"] == DBNull.Value ? string.Empty : dt.Rows[i]["Periode"].ToString();
                        p.Polarization = dt.Rows[i]["Polarization"] == DBNull.Value ? string.Empty : dt.Rows[i]["Polarization"].ToString();
                        
                        //p.UoM = new UoMController().GetUoMByID(dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString()));
                        //p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString()));
                        //p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString()));


                        p.UoMDetailMax = new UoMDetail();
                        p.UoMDetailMax.Description = dt.Rows[i]["DescriptionUoMDetailMax"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionUoMDetailMax"].ToString();

                        p.UoMDetailMin = new UoMDetail();
                        p.UoMDetailMin.Description = dt.Rows[i]["DescriptionUoMDetailMin"] == DBNull.Value ? string.Empty : dt.Rows[i]["DescriptionUoMDetailMin"].ToString();

                        p.TargetDescription = dt.Rows[i]["targetDescription"] == DBNull.Value ? string.Empty : dt.Rows[i]["targetDescription"].ToString();
                        p.ManualCalculation = dt.Rows[i]["ManualCalculation"] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i]["ManualCalculation"].ToString());
                        p.KPICalculationType = dt.Rows[i]["KPICalculationType"] == DBNull.Value ? string.Empty : dt.Rows[i]["KPICalculationType"].ToString();
                        p.Weight = dt.Rows[i]["Weight"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Weight"].ToString());
                        
                        //p.KPIParent = new KPIController().GetKPIByID(dt.Rows[i]["KPIParent"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIParent"].ToString()));
                        //p.KPIReplacement = new KPIController().GetKPIByID(dt.Rows[i]["KPIReplacement"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIReplacement"].ToString()));
                        
                        p.ReplacementDate = dt.Rows[i]["ReplacementDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["ReplacementDate"].ToString());
                        p.ReplacementDescription = dt.Rows[i]["ReplacementDescription"] == DBNull.Value ? string.Empty : dt.Rows[i]["ReplacementDescription"].ToString();
                        p.WeightEnableFlag = dt.Rows[i]["WeightEnableFlag"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = dt.Rows[i]["UoMEnableFlag"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["UoMEnableFlag"].ToString());
                        p.KPIDetailInformation = dt.Rows[i]["KPIDetailInformation"] == DBNull.Value ? string.Empty : dt.Rows[i]["KPIDetailInformation"].ToString();
                        p.KPIStrategicObjective = dt.Rows[i]["KPIStrategicObjective"] == DBNull.Value ? string.Empty : dt.Rows[i]["KPIStrategicObjective"].ToString();
                        //p.KPIChilds = new KPIController().GetKPIChilds(new KPI() { ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString()) });
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"] == DBNull.Value ? string.Empty : dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"] == DBNull.Value ? string.Empty : dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        p.ScoredBySystem = dt.Rows[i]["ScoredBySystem"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["ScoredBySystem"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return KPIs.ToList();
        }

        public IList<KPI> GetKPIChildTable(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, KPI Parent)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIChilds";
            command.Parameters.Add("@KPIPARENTID", SqlDbType.VarChar).Value = Parent.ID;

            DataSet dx = GetTable(command);

            List<KPI> KPIs = new List<KPI>();

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        KPI p = new KPI();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.Description = dt.Rows[i]["Description"].ToString();
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());

                        //p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                        //p.CategoryKPI = new CategoryKPIController().GetCategoryKPIByID(Convert.ToInt16(dt.Rows[i]["CategoryID"].ToString()));
                        //p.TypeKPI = new TypeKPIController().GetTypeKPIByID(Convert.ToInt16(dt.Rows[i]["TypeID"].ToString()));
                        //p.SubTypeKPI = new SubTypeKPIController().GetSubTypeKPIByID(Convert.ToInt16(dt.Rows[i]["SubTypeID"].ToString()));

                        //p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                        p.GroupKPI = new GroupKPI();
                        p.GroupKPI.Description = dt.Rows[i]["DescriptionGroup"].ToString();

                        //p.CategoryKPI = new CategoryKPIController().GetCategoryKPIByID(Convert.ToInt16(dt.Rows[i]["CategoryID"].ToString()));
                        p.CategoryKPI = new CategoryKPI();
                        p.CategoryKPI.Description = dt.Rows[i]["DescriptionCategory"].ToString();

                        //p.TypeKPI = new TypeKPIController().GetTypeKPIByID(Convert.ToInt16(dt.Rows[i]["TypeID"].ToString()));
                        p.TypeKPI = new TypeKPI();
                        p.TypeKPI.Description = dt.Rows[i]["DescriptionType"].ToString();

                        //p.SubTypeKPI = new SubTypeKPIController().GetSubTypeKPIByID(Convert.ToInt16(dt.Rows[i]["SubTypeID"].ToString()));
                        p.SubTypeKPI = new SubTypeKPI();
                        p.SubTypeKPI.Description = dt.Rows[i]["DescriptionSubType"].ToString();

                        p.Year = Convert.ToInt16(dt.Rows[i]["Year"].ToString());
                        p.Periode = dt.Rows[i]["Periode"].ToString();
                        p.Polarization = dt.Rows[i]["Polarization"].ToString();
                        
                        //p.UoM = new UoMController().GetUoMByID(dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString()));
                        //p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString()));
                        //p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString()));

                        p.UoMDetailMax = new UoMDetail();
                        p.UoMDetailMax.Description = dt.Rows[i]["DescriptionUoMDetailMax"].ToString();

                        p.UoMDetailMin = new UoMDetail();
                        p.UoMDetailMin.Description = dt.Rows[i]["DescriptionUoMDetailMin"].ToString();


                        p.TargetDescription = dt.Rows[i]["targetDescription"].ToString();
                        p.ManualCalculation = Convert.ToInt32(dt.Rows[i]["ManualCalculation"].ToString());
                        p.KPICalculationType = dt.Rows[i]["KPICalculationType"].ToString();
                        p.Weight = Convert.ToInt16(dt.Rows[i]["Weight"].ToString());
                        p.KPIParent = new KPIController().GetKPIByID(dt.Rows[i]["KPIParent"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIParent"].ToString()));
                        p.KPIReplacement = new KPIController().GetKPIByID(dt.Rows[i]["KPIReplacement"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIReplacement"].ToString()));
                        p.ReplacementDate = dt.Rows[i]["ReplacementDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["ReplacementDate"].ToString());
                        p.ReplacementDescription = dt.Rows[i]["ReplacementDescription"].ToString();
                        p.WeightEnableFlag = Convert.ToBoolean(dt.Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = Convert.ToBoolean(dt.Rows[i]["UoMEnableFlag"].ToString());
                        p.KPIDetailInformation = dt.Rows[i]["KPIDetailInformation"].ToString();
                        p.KPIStrategicObjective = dt.Rows[i]["KPIStrategicObjective"].ToString();
                        //p.KPIChilds = new KPIController().GetKPIChilds(new KPI() { ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString()) });
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        p.ScoredBySystem = Convert.ToBoolean(dt.Rows[i]["ScoredBySystem"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return KPIs.ToList();
        }

        public IList<KPI> GetKPIChilds(KPI KPIParent)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIChilds";
            command.Parameters.Add("@KPIPARENTID", SqlDbType.VarChar).Value = KPIParent.ID;

            DataSet dx = GetTable(command);

            List<KPI> KPIs = new List<KPI>();

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        KPI p = new KPI();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.Description = dt.Rows[i]["Description"].ToString();
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                        //p.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(dt.Rows[i]["GroupID"].ToString()));
                        //p.CategoryKPI = new CategoryKPIController().GetCategoryKPIByID(Convert.ToInt16(dt.Rows[i]["CategoryID"].ToString()));
                        //p.TypeKPI = new TypeKPIController().GetTypeKPIByID(Convert.ToInt16(dt.Rows[i]["TypeID"].ToString()));
                        //p.SubTypeKPI = new SubTypeKPIController().GetSubTypeKPIByID(Convert.ToInt16(dt.Rows[i]["SubTypeID"].ToString()));
                        p.Year = Convert.ToInt16(dt.Rows[i]["Year"].ToString());
                        p.Periode = dt.Rows[i]["Periode"].ToString();
                        p.Polarization = dt.Rows[i]["Polarization"].ToString();
                        
                        //p.UoM = new UoMController().GetUoMByID(dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString()));
                        //p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString()));
                        //p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString()));
                        
                        p.TargetDescription = dt.Rows[i]["targetDescription"].ToString();
                        p.ManualCalculation = Convert.ToInt32(dt.Rows[i]["ManualCalculation"].ToString());
                        p.KPICalculationType = dt.Rows[i]["KPICalculationType"].ToString();
                        p.Weight = Convert.ToInt16(dt.Rows[i]["Weight"].ToString());
                        p.KPIParent = KPIParent;
                        
                        //p.KPIReplacement = new KPIController().GetKPIByID(dt.Rows[i]["KPIReplacement"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIReplacement"].ToString()));
                        
                        p.ReplacementDate = dt.Rows[i]["ReplacementDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["ReplacementDate"].ToString());
                        p.ReplacementDescription = dt.Rows[i]["ReplacementDescription"].ToString();
                        p.WeightEnableFlag = Convert.ToBoolean(dt.Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = Convert.ToBoolean(dt.Rows[i]["UoMEnableFlag"].ToString());
                        p.KPIDetailInformation = dt.Rows[i]["KPIDetailInformation"].ToString();
                        p.KPIStrategicObjective = dt.Rows[i]["KPIStrategicObjective"].ToString();
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        p.ScoredBySystem = Convert.ToBoolean(dt.Rows[i]["ScoredBySystem"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            return KPIs.ToList();
        }

        public KPI GetKPIByID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GETKPIBYID";
            command.Parameters.Add("@ID", SqlDbType.Int).Value = ID;

            IList<string> Data = GetSingleRow(command);
            KPI KPIs = null;

            if (Data != null && Data.Count > 0)
            {
                KPIs = new KPI();
                KPIs.ID = Convert.ToInt16(Data[0].ToString());
                KPIs.Description = Data[1].ToString();
                KPIs.isActive = Convert.ToBoolean(Data[2].ToString());

                //KPIs.GroupKPI = new GroupKPIController().GetGroupKPIByID(Convert.ToInt16(Data[3].ToString()));
                //KPIs.CategoryKPI = new CategoryKPIController().GetCategoryKPIByID(Convert.ToInt16(Data[4].ToString()));
                //KPIs.TypeKPI = new TypeKPIController().GetTypeKPIByID(Convert.ToInt16(Data[5].ToString()));
                //KPIs.SubTypeKPI = new SubTypeKPIController().GetSubTypeKPIByID(Convert.ToInt16(Data[6].ToString()));
                
                KPIs.Year = Convert.ToInt16(Data[7].ToString());
                KPIs.Periode = Data[8].ToString();
                KPIs.Polarization = Data[9].ToString();
                
                //KPIs.UoM = new UoMController().GetUoMByID(Data[10] == string.Empty ? 0 : Convert.ToInt16(Data[10].ToString()));
                //KPIs.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(Data[11] == string.Empty ? 0 : Convert.ToInt16(Data[11].ToString()));
                //KPIs.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(Data[12] == string.Empty ? 0 : Convert.ToInt16(Data[12].ToString()));
                
                KPIs.TargetDescription =   Data[13].ToString();
                KPIs.ManualCalculation = Data[14]==string.Empty ? 0 : Convert.ToInt32(Data[14].ToString());
                KPIs.KPICalculationType = Data[15].ToString();
                KPIs.Weight = Data[16]==string.Empty ? 0 : Convert.ToInt16(Data[16].ToString());
                
                //KPIs.KPIParent = new KPIController().GetKPIByID(Data[17] == string.Empty ? 0 : Convert.ToInt16(Data[17].ToString()));
                //KPIs.KPIReplacement = new KPIController().GetKPIByID(Data[18] == string.Empty ? 0 : Convert.ToInt16(Data[18].ToString()));
                
                KPIs.ReplacementDate = Data[19] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[19].ToString());
                KPIs.ReplacementDescription = Data[20].ToString();
                KPIs.WeightEnableFlag = Data[21]==string.Empty ? false : Convert.ToBoolean(Data[21].ToString());
                KPIs.UoMEnableFlag = Data[22] == string.Empty ? false : Convert.ToBoolean(Data[22].ToString());
                KPIs.KPIDetailInformation = Data[23].ToString();
                KPIs.KPIStrategicObjective = Data[24].ToString();
                KPIs.LastModifiedDate = Data[25] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[25].ToString());
                KPIs.LastModifiedBy = Data[26].ToString();
                KPIs.CreateDate = Data[27] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[27].ToString());
                KPIs.CreateBy = Data[28].ToString();
                
                //KPIs.KPIChilds = new KPIController().GetKPIChilds(new KPI() { ID = Convert.ToInt16(Data[0].ToString()) });

                KPIs.ScoredBySystem = Data[29] == string.Empty ? false : Convert.ToBoolean(Data[29].ToString());
                KPIs.Sequence = Data[30] == string.Empty ? 0 : Convert.ToInt32(Data[30].ToString());
                KPIs.EmployeeIDVerifier = Data[31] == string.Empty ? 0: Convert.ToInt32(Data[31].ToString());
                KPIs.Method.MethodName = Data[32].ToString();
                KPIs.isLockWeight = Data[29] == string.Empty ? false : Convert.ToBoolean(Data[33].ToString());
                KPIs.AssignWeight = Convert.ToString(Data[34].ToString());
                KPIs.PercentageMax = Data[35] == string.Empty ? 0 : Convert.ToInt32(Data[35].ToString());
                KPIs.PercentageMin = Data[36] == string.Empty ? 0 :  Convert.ToInt32(Data[36].ToString());

                KPIs.GroupKPI = new GroupKPI();
                KPIs.GroupKPI.ID = Convert.ToInt16(Data[3].ToString());
                KPIs.GroupKPI.Description = Data[37].ToString();

                KPIs.CategoryKPI = new CategoryKPI();
                KPIs.CategoryKPI.ID = Convert.ToInt16(Data[4].ToString());
                KPIs.CategoryKPI.Description = Data[38].ToString();

                KPIs.TypeKPI = new TypeKPI();
                KPIs.TypeKPI.ID = Convert.ToInt16(Data[5].ToString());
                KPIs.TypeKPI.Description = Data[39].ToString();

                KPIs.SubTypeKPI = new SubTypeKPI();
                KPIs.SubTypeKPI.ID = Convert.ToInt16(Data[6].ToString());
                KPIs.SubTypeKPI.Description = Data[40].ToString();

                KPIs.UoM = new UoM();
                KPIs.UoM.ID = Convert.ToInt16(Data[3].ToString());
                KPIs.UoM.Description = Data[41].ToString();

                KPIs.UoMDetailMax = new UoMDetail();
                KPIs.UoMDetailMax.ID = Convert.ToInt16(Data[11].ToString());
                KPIs.UoMDetailMax.Description = Data[42].ToString();

                KPIs.UoMDetailMin = new UoMDetail();
                KPIs.UoMDetailMin.ID = Convert.ToInt16(Data[12].ToString());
                KPIs.UoMDetailMin.Description = Data[43].ToString();

                KPIs.SignEmployeeID = Convert.ToInt32(Data[44].ToString());

            }

            return KPIs;
        }

        public IList<string> GetPickedKPIAndTotalWeight(Employee User)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetPickedKPIAndTotalWeight";
            command.Parameters.Add("@USERID", SqlDbType.Int).Value = User.ID;

            IList<string> Result = GetSingleRow(command);
            return Result;
        }
        
        public bool ValidateKPIParentAndChildAssignment(Employee AssignTo, KPI KPIs)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_ValidateKPIParentAndChildAssignment";
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = AssignTo.ID;
            command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KPIs.ID;

            return Convert.ToBoolean(GetSingleData(command));
        }
    }
}